{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "dd3463b3-da5a-4a6b-88ec-0506aeae0cfe",
   "metadata": {},
   "source": [
    "# 按天下载"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "23f4bc89-56c5-46f2-8dd2-efcbef18c88f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-21T03:34:13.390507Z",
     "start_time": "2024-03-21T03:34:13.318355Z"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import time\n",
    "\n",
    "\n",
    "def mysql_engine(host, port, user, passwd, db=None):\n",
    "    from sqlalchemy import create_engine\n",
    "    try:\n",
    "        engine = create_engine(f'mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}')\n",
    "        return engine\n",
    "    except Exception as e:\n",
    "        print(f\"An error occurred: {e}\")\n",
    "# mysql_rule = mysql_engine('172.20.1.129',3306,'rule','Z3Vl7enFj2eqkeJf','rule')\n",
    "mysql_rule = mysql_engine('47.253.56.86',4001,'longxiaolei','WWsCQ9TXG+BJBHlK','rule')\n",
    "from datetime import datetime, timedelta  \n",
    "\n",
    "# 文件夹中起止数据的下载时间，首次全量下载，后续可以调整为开始时间以介于更新时间的耗时\n",
    "start_date = datetime(2024, 3, 20)   \n",
    "# start_date = datetime(2024, 1, 28)   \n",
    "# start_date = datetime(2023, 10, 24)   \n",
    "\n",
    "end_date = datetime.today()  \n",
    "\n",
    "#  循环起止时间的日期，下载数据到相应的目录\n",
    "for single_day in (start_date + timedelta(days=x) for x in range(0, (end_date - start_date).days + 1)):  \n",
    "    # 格式化日期为字符串  \n",
    "    date_string = single_day.strftime(\"%Y-%m-%d\")  \n",
    "    next_day = single_day+timedelta(days=1)  #min(single_day+timedelta(days=1),end_date)\n",
    "    \n",
    "    next_day_str = next_day.strftime(\"%Y-%m-%d\")  \n",
    "    \n",
    "    sql = f\"\"\"select a.id,a.merchant_id,a.tx_id,a.req_data,a.resp_data,a.create_time,a.update_time, \n",
    "            case when a.type is null then b.type else a.type end as type\n",
    "    from risk.t_risk_urule_req_log a left join risk.t_risk_req_record b on a.tx_id = b.tx_id and a.merchant_id = b.merchant_id\n",
    "    where a.create_time >= '{date_string}' and a.create_time < '{next_day_str}' and a.tx_id is not null\n",
    "    \"\"\"\n",
    "    start_time = time.time()\n",
    "    df = pd.read_sql(sql,mysql_rule)\n",
    "    end_time = time.time()\n",
    "    print(f\"{date_string},{df.shape},查询耗时{end_time-start_time}\")\n",
    "    merchant_ids =  df['merchant_id'].unique()\n",
    "    types  = df['type'].unique()\n",
    "    len_arr = [0]\n",
    "    for merchant_id in merchant_ids : \n",
    "        for t in types:\n",
    "            # 注意在savepath中设定保存的路径和文件名\n",
    "            df_new = df[(df['merchant_id']==merchant_id)&(df['type']==t)]\n",
    "            if df_new.shape[0]>0:\n",
    "                len_arr.append(df_new.shape[0])\n",
    "                save_path = f\"data/{merchant_id}_type{t}_{date_string}.parquet\"\n",
    "                df_new.to_parquet(save_path,compression='zstd')\n",
    "                print(save_path,df_new.shape)\n",
    "    if sum(len_arr ) == df.shape[0]:\n",
    "        print('通过基本数据检查========>')\n",
    "    else:\n",
    "        print(\"^^^^^^^^^^^^^数据条数检查失败^^^^^^^^^^^^^\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "384px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
